手っ取り早くPythonでCSVデータ整形 #マクロから移行
Guten Abend, ベルリンの伊藤です。先々週ウィーンでヨーロッパメンバー大集合大会があり、スケートをしました。ウィーンもベルリンも冬は真グレーですが、楽しくやっています！
今回は Python 初心者に向けた記事です。
さて、前回こんな記事を書きました。
クラメソヨーロッパで使う銀行と会計ツールが現時点で取引の自動連携に対応しておらず、取引のCSVデータをダウンロードし、会計ツール用に整形し、取り込まなければいけないという状況だったので、そのCSV整形処理をマクロ(GAS)で作った内容をご紹介しました。
暫定処理で手っ取り早くやれる方法ということで、いつかPythonとかに移行したいなぁなんて話をしていましたが、意外と早々にPythonの方で実現できました！（せっかく使ったマクロはほんの数回しか使ってないけど、GASの使い勝手が学べたということで...）
流れ
Pythonのコードでやってるコアな処理としては以下の流れです。
- 書き出しファイルにヘッダを記述
- 指定列のタイムスタンプの表示形式を変更
- 指定列を編集・結合 x 3回
- 整形したデータを書き出しファイルへ出力
マクロでやった時の記事と比較すると分かりますが、前後でのマニュアル作業がなくなり、またいろいろパワーアップしました。
スクリプト
python csv_manipulate.py input.csv のように指定することで、同じディレクトリ内に整形されたcsvファイルが qonto-lo_20200130.csv のようなファイル名で書き出されます。
入出力ファイルを開き、ヘッダーを書き込み、readlineやreadlinesを使ってレコードを読み取り、for文で1レコードずつ処理して書き出した後、ファイルを閉じて終了するという大枠はこちらの記事: pythonでcsvファイルの編集をする - memopy を大変参考にさせていただきました。その辺の詳しい解説もそちらをご覧ください。
# -*- coding:utf-8 -*-
import re, sys, datetime
# 入出力ファイルの定義
file = open(sys.argv[1],"r")
now = datetime.datetime.now()
out_filename = 'qonto-lo_{0:%Y%m%d}.csv'.format(now)
out_file = open(out_filename,"w")
# 出力ファイルへヘッダー記述
out_file.write("\"Wertstellungsdatum\",\"Buchungsdatum\",\"Auftraggeber/Empfänger\",\"Verwendungszweck\",\"Betrag\",\"Zusatzinfo\"\n")
# 入力ファイルでヘッダー行を飛ばした後、全行を読み取り、for文で1行ずつ整形&出力
file.readline()
lines = file.readlines()
for line in lines:
# 先頭と末尾のダブルクォーテーションをブランクに置換する
line = re.sub('^"|"\n', '', line)
# ダブルクォーテーション&カンマ区切りでリストに変換する
line = line.split("\",\"")
# settled 状態でない場合はコンソールに表示し、スキップ
if line[0] != "settled":
record = "\"{}\",\"{}\",\"{}\",\"{}\"".format(
line[0],
line[3],
line[6],
line[12]
)
print("Not settled record:" + record)
continue
# 日付型の変換(dd-mm-yyyy HH:mm:ss --> yyyy-mm-dd HH:mm:ss)
dt1 = datetime.datetime.strptime(line[3], "%d-%m-%Y %H:%M:%S")
dt2 = datetime.datetime.strptime(line[5], "%d-%m-%Y %H:%M:%S")
# 列の結合その１(IF(comment="",transaction_method+transaction_iniator,comment))
if len(line[10]) != 0:
merge1 = line[10]
else:
merge1 = line[7] + " " + line[8]
if len(line[11]) != 0:
merge1 = merge1 + " " + line[11]
if merge1 == " ":
merge1 = "_"
# 金額の変換(支出-/収入+ --> 支出+/収入-)
amt = float(line[12]) * -1
lamt = float(line[14]) * -1
# 列の結合その２(IF(local_currency="EUR",,local_amount&" "&local_currency&" ")&vat_amount&" "&vat_country)
if line[15] != "EUR":
merge2 = line[15] + " " + str(lamt)
else:
merge2 = ""
if len(line[16]) != 0:
merge2 = merge2 + "VAT " + line[16] + " " + line[17]
if len(merge2) == 0:
merge2 = "_"
# 変換処理した値をダブルクォーテーション囲み、カンマ区切りで入れ込む
row = "\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\"\n".format(
dt1, # Wertstellungsdatum
dt2, # Buchungsdatum
line[6], # Auftraggeber/Empfänger
merge1, # Verwendungszweck
amt, # Betrag
merge2 # Zusatzinfo
)
# 出力ファイルに書き出し
out_file.write(row)
# ２つのファイルを閉じる
file.close()
out_file.close()
補足
以下、参考記事 様からカスタマイズした部分を補足します。
入出力ファイルの定義
file = open(sys.argv[1],"r")
sys.argv[1] によりPython実行時のパラメータで入力ファイル名を渡すようにしています。
now = datetime.datetime.now()
out_filename = 'qonto-lo_{0:%Y%m%d}.csv'.format(now)
out_file = open(out_filename,"w")
また、出力ファイル名に日付を含められればと思ったので、datetime で実行時点の日時を取得し、ファイル名の{0:%Y%m%d}の部分にYYYYMMDD形式で入れています。
ファイル名に日付の参考: https://tonari-it.com/python-datetime-now/
for文で1行ずつ整形
# 先頭と末尾のダブルクォーテーションをブランクに置換する
line = re.sub('^"|"\n', '', line)
# ダブルクォーテーション&カンマ区切りでリストに変換する
line = line.split("\",\"")
今回のケースでは入力ファイルが " (ダブルクォーテーション) 囲み、 , (カンマ) 区切りだったので、整形の前処理としてそれらを取っ払って値だけリストに格納する処理を行なっています。参考記事でも同様の処理をしていますが、" 囲みがないケースだったため、上のように修正を加えています。
settled 状態でない場合はコンソールに表示し、スキップ
これは前回のマクロ記事では入れてない処理（そういうレコードがあると気付いてなかった...）ですが、取引レコードでステータスが「settled」でなくレコードの情報も完全ではないケースがありました。この対策として、次の処理を入れました。
if line[0] != "settled":
record = "\"{}\",\"{}\",\"{}\",\"{}\"".format(
line[0],
line[3],
line[6],
line[12]
)
print("Not settled record:" + record)
continue
ステータスの値が「settled」ではない場合、レコードの一部を実行画面で表示（print）させ、後続の整形&ファイル出力の処理を飛ばし（continue）ます。該当するレコードは出力ファイルには書き込まれず、以下のように表示されます。
$ python csv_manipulate.py input.csv Not settled record:"processing","17-12-2019 10:33:42","amazn europe","-52.80"
for 文スキップ continue の参考: https://www.headboost.jp/python-for-continue/
日付型の変換
dt1 = datetime.datetime.strptime(line[3], "%d-%m-%Y %H:%M:%S")
dt2 = datetime.datetime.strptime(line[5], "%d-%m-%Y %H:%M:%S")
入力ファイルの日時が dd-mm-yyyy HH:mm:ss 表記なので、出力ファイルで yyyy-mm-dd HH:mm:ss となるよう、変換して変数に代入しています。
なお、この strptime はデフォルトが yyyy-mm-dd HH:mm:ss なので特に指定が不要でしたが、他の形式へ変更したい場合にはさらに strftime を使って次のように指定します。（yyyy/mm/dd HH:mm:ssの例）
dt1 = dt1.strftime('%Y/%m/%d %H:%M:%S') # デフォルトからフォーマットを明示的に指定が必要な場合のみ。
日付型変換の参考: https://pg-chain.com/python-datetime-strptime#toc1
列の結合その１
if len(line[10]) != 0:
merge1 = line[10]
else:
merge1 = line[7] + " " + line[8]
if merge1 == " ":
merge1 = "_"
ここでの処理はかなり固有ですが...
コメント（line[10]）に値がある場合はコメントの値を、値がない場合は取引方法（line[7]）と取引担当者（line[8]）の値を結合して入れ、変数 merge1 と定義しています。
また出力ファイルを取り込む会計ツールの仕様で値が NULL あるいはスペースだけのレコードがあると、該当レコードだけ飛ばされて何のエラーもメッセージも出してくれない動きをするので、その対策として必ず値が入るよう、念のために値がスペースの場合は _ を入れています。
文字列NULL判定の参考: https://pg-chain.com/python-null
金額の変換
amt = float(line[12]) * -1
lamt = float(line[14]) * -1
入力ファイルでは支出-/収入+と表示されていますが、取り込む会計ツールの出力ファイルには支出+/収入-で定義せねばなりません。ということで、+/-の変換のため、文字列を数値ととらえ（float）、-1をかけています。
文字列-数値変換の参考: https://www.javadrive.jp/python/num/index6.html
列の結合その２
if line[15] != "EUR":
merge2 = line[15] + " " + str(lamt)
else:
merge2 = ""
if len(line[16]) != 0:
merge2 = merge2 + "VAT " + line[16] + " " + line[17]
if len(merge2) == 0:
merge2 = "_"
その１と同様のことをしています。現地通貨（line[15]）が「EUR」でなければ、その現地通貨と現地通貨の金額 (str(lamt)) の値を変数 merge2 と定義しています。（str(lamt) は前述で+/-変換した後の値を、今度は数値を文字列として扱うようにしています。）さらに、VAT金額（line[16]）に値がある場合には、加えてVAT金額とVATの国（line[17]）の値も入れます。
こちらも、すべて値が空だった場合のために "_" を入れています。
サンプル
※フォーマットはそのまま、データはサンプルです。
試しに「列の結合その１」に関わる列の値を消してみましたが、きちんと「_」が入りました。
初歩的な内容かもしれませんが、同じように困っている方に一部でもご参考いただければ幸いです！
![[初学者向け] IAM 設計の観点解説とテンプレート化してみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-7bf2dfb6b7a0dd9b7561e87b396ccdc3/018019c86ac567eceba7268550750d9f/aws-iam)
